A join index (JI) is a cross between a database view and an index. It is like a database view because it is defined via a database query. It is like an index because of the way the underlying database system processes it, such as when underlying tables associated with the JI change the JI is automatically updated so that each time a JI is processed, the data returned is up-to-date.
A JI or materialized view is widely used in database systems to improve query performance. A sparse JI on which a WHERE clause is defined is particularly useful for users to create a window into a large table for the data of interest. For example, in an Active Data Warehouse environment, while the transaction table contains records of all transactions that have ever taken place, certain applications may only need to look at transactions that have taken place in the past 3 days. For such applications, users may choose to create a sparse JI with a date range of “BETWEEN CURRENT_DATE—interval ‘3’ days AND CURRENT_DATE—interval ‘1’ day.” The need for users to define a sparse JI based on the system-defined constants, CURRENT_DATE (CD) and/or CURRENT_TIMESTAMP (CT), increases with the advent of temporal databases. One problem with sparse JI based on CD or CT is that data in the sparse JI becomes out-of-date over time. It may contain historical data that is no longer of interest to the users or it may not have the more recent data that is of interest to the users. The former may result in a large JI structure that degrades the performance of JI. The later may render the JI to be unusable as it doesn't have the recent data to cover user queries.
Some database features provided an ALTER <JI> TO CURRENT statement that allows users the ability to refresh the content of a JI by moving CD and/or CT in a JI definition to the current date and/or time. A straightforward approach of handling this kind of ALTER statement is to drop the JI and recreate it with the current value of CD and/or CT. However, this is not an efficient approach since dropping and re-creating a JI involves a lot of data dictionary operations and materializing a new JI can be very time-consuming.
Therefore, it is desirable to devise an algorithm to incrementally maintain existing JI without dropping and recreating it just for refreshing the CD and/or CT. As is known, CD and/or CT can appear in almost anywhere in a JI. More specifically, CD and/or CT can appear in the following Structure Query Language (SQL) constructs:                SELECT List;        WHERE clause;        ON clause;        GROUP BY clause;        ORDER BY clause;        PRIMARY INDEX clause; and        PARTITION BY clause.        
How the content of a JI is affected by an ALTER statement depends on the clause(s) that CD and/or CT are used in. The incremental maintenance algorithms for ALTER statements should be tuned to catch these differences in order to minimize the JI maintenance costs.